package vn.com.pavibook.daoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import vn.com.pavibook.dao.PublisherDao;
import vn.com.pavibook.domain.model.PagingInfo;
import vn.com.pavibook.domain.model.Publisher;
import vn.com.pavibook.domain.model.SearchResult;
import vn.com.pavibook.utils.DaoHelper;

@Repository
public class PublisherDaoImpl extends DaoHelper<Publisher> implements
		PublisherDao {

	@Override
	public void addNewPublisher(Publisher publisher) {
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO publisher (name, title, address, boss_name, phone, email_bussiness, account_id, register_date, active, description)");
		sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		Object[] params = new Object[] { publisher.getName(),
				publisher.getTitle(), publisher.getAddress(),
				publisher.getBossName(), publisher.getPhone(),
				publisher.getEmailBussiness(), publisher.getAccountId(),
				publisher.getRegisterDate(), publisher.getActive(),
				publisher.getDescription() };
		insert(sql.toString(), params);
	}

	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Publisher> getPublisher(PagingInfo pagingInfo,
			String keyword) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM publisher WHERE active = 1 ");
		if (StringUtils.isNotEmpty(keyword)) {
			sql.append("AND (");
			String[] keyStrings = keyword.split(" ");
			for (int i = 0; i < keyStrings.length; i++) {
				String s = "'%" + keyStrings[i] + "%'";
				if (i == 0) {
					sql.append(" name LIKE " + s + " OR address LIKE " + s);
				} else {
					sql.append("OR name LIKE " + s + " OR address LIKE " + s);
				}
			}
			sql.append(")");
		}
		sql.append(" ORDER BY register_date DESC");
		return queryPaging(sql.toString(), null, new PublisherRowMaper(),
				pagingInfo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Publisher> getPersonalInfo(String username) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM publisher PB left join account AC on PB.account_id = AC.id WHERE AC.username = ?");
		Object[] param = new Object[] { username };

		return query(sql.toString(), param, new PublisherRowMaper());
	}

	@SuppressWarnings("rawtypes")
	class PublisherRowMaper implements RowMapper {
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Publisher publisher = new Publisher();
			publisher.setId(rs.getInt("id"));
			publisher.setAccountId(rs.getInt("account_id"));
			publisher.setActive(rs.getBoolean("active"));
			publisher.setAddress(rs.getString("address"));
			publisher.setBossName(rs.getString("boss_name"));
			publisher.setDescription(rs.getString("description"));
			publisher.setEmailBussiness(rs.getString("email_bussiness"));
			publisher.setName(rs.getString("name"));
			publisher.setPhone(rs.getString("phone"));
			publisher.setRegisterDate(rs.getDate("register_date"));
			publisher.setTitle(rs.getString("title"));
			return publisher;
		}
	}

	@Override
	public boolean isExistedUsername(String username) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT count(id) FROM account WHERE username = '"
				+ username + "'");
		int rowNum;
		try {
			rowNum = getJdbcTemplate().queryForInt(sql.toString());
		} catch (DataAccessException e) {
			return true;
		}
		if (rowNum > 0) {
			return true;
		}
		return false;
	}

	@Override
	public void updatePublisherInfo(Publisher publisher) {
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE publisher set name = ?, address = ?, phone = ?, email_bussiness = ?, description = ?");
		sql.append(" WHERE id = ?");
		Object[] params = new Object[] { publisher.getName(),
				publisher.getAddress(), publisher.getPhone(),
				publisher.getEmailBussiness(), publisher.getDescription(),
				publisher.getId() };
		insert(sql.toString(), params);
	}

	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<Publisher> getPersonalInfoById(int id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM publisher WHERE id = ?");
		Object[] param = new Object[] { id };
		return query(sql.toString(), param, new PublisherRowMaper());
	}

	@Override
	public int getIdByUsername(String username) {
		String sql = "SELECT p.id FROM publisher p inner join account a on p.account_id = a.id WHERE a.username = ?";
		Object[] params = new Object[]{username};
		return getJdbcTemplate().queryForInt(sql, params);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public String getNameByUsername(String username) {
		String sql = "SELECT p.* FROM publisher p inner join account a on p.account_id = a.id WHERE a.username = ?";
		Object[] params = new Object[]{username};
		SearchResult<Publisher> result = query(sql.toString(), params, new PublisherRowMaper());
		Publisher publisher= result.getSearchResult().get(0);
		return publisher.getName();
	}

	@Override
	public void deletePublisherById(int id) {
		String sql= "Update publisher set active = 0 where id = ?";
		Object[] params = new Object[] {id};
		insert(sql, params);
	}
}
